﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace NCRGEA.AAL
{
    public static class DAL
    {
        private static string SQL_CONNECTION_STRING = System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString;

        public static bool ExecuteQuery_ValidateRowCount(string query, int rowCount)
        {
            DataTable tbl = ExecuteQuery(query);
            if (tbl == null || tbl.Rows.Count != rowCount)
                return false;
            return true;
        }

        public static object DBN(object a)
        {
            if (a == null)
                return DBNull.Value;
            return a;
        }

        public static bool ExecuteQuery_ValidateRowCount(string query, SqlParameter[] sParams, int rowCount)
        {
            DataTable tbl = ExecuteQuery(query, sParams);
            if (tbl == null || tbl.Rows.Count != rowCount)
                return false;
            return true;
        }

        /// <summary>
        /// Executes a query and returns the results.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static DataTable ExecuteQuery(string query)
        {
            return ExecuteQuery(query, null);
        }

        public static DataTable ExecuteQuery(string query, SqlParameter[] sParams)
        {
            SqlCommand sqlCmd = new SqlCommand(query);
            if (sParams != null)
                foreach (SqlParameter s in sParams)
                    if (s != null)
                        sqlCmd.Parameters.Add(s);
            return ExecuteQuery(sqlCmd);
        }

        public static DataTable ExecuteQuery(SqlCommand sqlCmd)
        {
            DataTable result = new DataTable();
            if (sqlCmd == null)
                return result;
            using (SqlConnection sqlConn = new SqlConnection(SQL_CONNECTION_STRING))
            {
                try
                {
                    //Only open a new sql connection if this SqlCommand doesn't have one
                    if (sqlCmd.Connection == null || sqlCmd.Connection.State != ConnectionState.Open)
                    {
                        sqlConn.Open();
                        sqlCmd.Connection = sqlConn;
                    }
                    result.Load(sqlCmd.ExecuteReader());
                }
                catch (Exception eX)
                {
                    Logger.Log("Error executing query: " + eX.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// Executes a query and returns the number of rows affected.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, null);
        }

        public static int ExecuteNonQuery(string query, SqlParameter[] sParams)
        {
            int result = -1;
            using (SqlConnection sqlConn = new SqlConnection(SQL_CONNECTION_STRING))
            {
                SqlCommand sqlCmd = new SqlCommand(query, sqlConn);
                if (sParams != null)
                    foreach (SqlParameter s in sParams)
                        if (s != null)
                            sqlCmd.Parameters.Add(s);
                        else
                            Logger.Log("Null SQL Parameter encountered in ExecuteNonQuery()! Query: " + query);

                try
                {
                    sqlConn.Open();
                    result = sqlCmd.ExecuteNonQuery();
                }
                catch (Exception eX)
                {
                    Logger.Log("Error executing non-query: " + eX.Message);
                }
            }
            return result;
        }

        public static SqlConnection GetSqlConnection()
        {
            SqlConnection sqlConn = new SqlConnection(SQL_CONNECTION_STRING);
            try
            {
                sqlConn.Open();
            }
            catch (Exception eX)
            {
                Logger.Log(eX.Message);
            }
            if (sqlConn.State == ConnectionState.Open)
                return sqlConn;
            return null;
        }
    }
}
